package com.fuershiye.woman.monitor.dao;

import com.fuershiye.woman.monitor.beans.IdNameCode;
import com.fuershiye.woman.monitor.beans.IdNamePid;
import com.fuershiye.woman.monitor.beans.Page;
import com.fuershiye.woman.monitor.model.Indicator;
import com.fuershiye.woman.monitor.utils.DaoSqlUtil;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigInteger;
import java.util.List;

@Repository("indicatorDao")
public class IndicatorDao {

    @Autowired
    private HibernateDao hibernateDao;

    public void save(Indicator indicator){
        hibernateDao.save(indicator);
    }

    public void delete(Long id) {
        String sql = "update Indicator set status = 0 where id = ?";
        SQLQuery query = hibernateDao.getSqlQuery(sql).addEntity(Indicator.class);
        query.setLong(0, id);
        query.executeUpdate();
    }

	public Indicator get(Long id) {
		return hibernateDao.getById(Indicator.class,id);
	}

    @SuppressWarnings("unchecked")
	public Indicator findByCodeForms(String code, long formsid) {

        StringBuffer sb = new StringBuffer("from Indicator where 1=1 ");

		sb = DaoSqlUtil.appendSql(sb, "code", code);
		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);

        Query query = hibernateDao.getQuery(sb.toString());
		query = DaoSqlUtil.appendQuery(query, "code", code);
		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);

        List<Indicator> result = query.list();
        if(result==null||result.size()<1){
        	return null;
        }
        return result.get(0);
    }
    @SuppressWarnings("unchecked")
	public Long getIdByCodeForms(String code, long formsid) {

		StringBuffer sb = new StringBuffer("select id from indicator where 1=1 ");
		sb = DaoSqlUtil.appendSql(sb, "code", code);
		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);

		Query query = hibernateDao.getSqlQuery(sb.toString());
		query = DaoSqlUtil.appendQuery(query, "code", code);
		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);

        List<BigInteger> result = query.list();
        if(result==null||result.size()<1){
        	return null;
        }
		BigInteger count = result.get(0);
        return count.longValue();
    }

	@SuppressWarnings("unchecked")
    public Indicator getTop() {
    	
    	String hql = "from Indicator ORDER BY sort DESC";
    	Query query = hibernateDao.getQuery(hql);
    	query.setFirstResult(0);
    	query.setMaxResults(1);
    	List<Indicator> result = query.list();
    	if(result==null||result.size()<1){
    		return null;
    	}
    	return result.get(0);
    }
    
    @SuppressWarnings("unchecked")
    public Indicator findBySort(Long pos, Integer aim, Integer count) {
		
    	StringBuffer sb = new StringBuffer("from Indicator where sort "); 
		if(aim==1){
			sb.append(">:sort order by sort asc ");
		}else{
			sb.append("<:sort order by sort desc ");
		}
        Query query = hibernateDao.getQuery(sb.toString());
        query.setParameter("sort", pos);
        query.setFirstResult(0);
        query.setMaxResults(count);
        
        List<Indicator> result = query.list();
        if(result==null||result.size()<1){
        	return null;
        }
        return result.get(0);
    }
    
    @SuppressWarnings("unchecked")
    public List<Indicator> getByPid(Long pid) {
    	String hql = "from Indicator where pid = :pid ORDER BY sort DESC";
    	Query query = hibernateDao.getQuery(hql);
		query.setLong("pid",pid);
    	return query.list();
    }

	@SuppressWarnings("unchecked")
	public List<Indicator> listByConditionOppositeProvince(String name, Long formsid, List<Long> types, Integer province, Integer sex, Integer status, Page page) {

		StringBuffer sb = new StringBuffer("from Indicator where 1=1 ");
		if(StringUtils.isNotBlank(name)){
			sb.append("and (name like:name or code like:name) ");
		}
		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSqlListLong(sb, "type", types);
		sb = DaoSqlUtil.appendSqlOpposite(sb, "province", province);
		sb = DaoSqlUtil.appendSqlOpposite(sb, "sex", sex);
		sb = DaoSqlUtil.appendSql(sb, "status", status);
		sb.append("ORDER BY code asc ");

		Query query = hibernateDao.getQuery(sb.toString());

		query = DaoSqlUtil.appendQuery(query, "name", name);
		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQueryListLong(query, "type", types);
		query = DaoSqlUtil.appendQuery(query, "province", province);
		query = DaoSqlUtil.appendQuery(query, "sex", sex);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		if(page!=null){
			query.setFirstResult(page.getStart());
			query.setMaxResults(page.getPageSize());
		}

		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<Indicator> listByCondition(String name, Integer country, Long formsid, Integer monitor, Long pid, List<Long> types, Integer province, Integer sex, Integer status, Page page) {

		StringBuffer sb = new StringBuffer("from Indicator where 1=1 ");
		if(StringUtils.isNotBlank(name)){
			sb.append("and (name like:name or code like:name) ");
		}
		sb = DaoSqlUtil.appendSql(sb, "country", country);
		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSql(sb, "monitor", monitor);
		sb = DaoSqlUtil.appendSql(sb, "pid", pid);
		sb = DaoSqlUtil.appendSqlListLong(sb, "type", types);
//		sb = DaoSqlUtil.appendSql(sb, "type", type);
		sb = DaoSqlUtil.appendSql(sb, "province", province);
		sb = DaoSqlUtil.appendSql(sb, "sex", sex);
		sb = DaoSqlUtil.appendSql(sb, "status", status);
		sb.append("ORDER BY code asc ");

		Query query = hibernateDao.getQuery(sb.toString());

		query = DaoSqlUtil.appendQuery(query, "country", country);
		query = DaoSqlUtil.appendQuery(query, "name", name);
		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQuery(query, "monitor", monitor);
		query = DaoSqlUtil.appendQuery(query, "pid", pid);
		query = DaoSqlUtil.appendQueryListLong(query, "type", types);
		query = DaoSqlUtil.appendQuery(query, "province", province);
		query = DaoSqlUtil.appendQuery(query, "sex", sex);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		if(page!=null){
			query.setFirstResult(page.getStart());
			query.setMaxResults(page.getPageSize());
		}
		return query.list();
	}

    @SuppressWarnings("unchecked")
    public List<IdNamePid> getAllNames() {

        List<IdNamePid> result = Lists.newArrayList();
        
        String hql = "select id,name,pid from indicator ORDER BY sort DESC";
        Query query = hibernateDao.getSqlQuery(hql);
        List<Object[]> queryResult = query.list();
        
        for (Object[] fields : queryResult) {
        	IdNamePid idAndName = new IdNamePid();
        	idAndName.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
        	idAndName.setName(fields[1] == null? "":fields[1].toString());
        	idAndName.setPid(fields[2] == null? 0:Long.parseLong(fields[2].toString()));
    	  	result.add(idAndName);
		}
	  	return result;
    }
    
    @SuppressWarnings("unchecked")
    public List<Indicator> getAllIndicator() {
    	String hql = "from Indicator";
    	Query query = hibernateDao.getQuery(hql);
    	return query.list();
    }
    
    @SuppressWarnings("unchecked")
    public List<IdNameCode> list() {
    	
    	List<IdNameCode> result = Lists.newArrayList();

    	StringBuffer sb = new StringBuffer("select id,name,code from indicator ORDER BY sort ");
    	Query query = hibernateDao.getSqlQuery(sb.toString());
    	
    	List<Object[]> queryResult = query.list();
    	
    	for (Object[] fields : queryResult) {
    		IdNameCode idNameCode = new IdNameCode();
    		idNameCode.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
    		idNameCode.setName(fields[1] == null? "":fields[1].toString());
    		idNameCode.setCode(fields[2] == null? "":fields[2].toString());
    		result.add(idNameCode);
    	}
    	return result;
    }

	@SuppressWarnings("unchecked")
	public List<Indicator> listByCondition(Long type, Integer status, Page page) {

		StringBuffer sb = new StringBuffer("from Indicator where 1=1 ");
		sb = DaoSqlUtil.appendSql(sb, "type", type);
		sb = DaoSqlUtil.appendSql(sb, "status", status);
		sb.append("ORDER BY code asc ");

		Query query = hibernateDao.getQuery(sb.toString());
		query = DaoSqlUtil.appendQuery(query, "type", type);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		if(page!=null){
			query.setFirstResult(page.getStart());
			query.setMaxResults(page.getPageSize());
		}

		return query.list();
	}

	@SuppressWarnings("unchecked")
	public List<IdNamePid> listInCondition(List<Integer> monitor, Long formsid, Long type, Integer status) {

		List<IdNamePid> result = Lists.newArrayList();

		StringBuffer sb = new StringBuffer("select id,name,pid from indicator where 1=1 ");

		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSqlListInt(sb, "monitor", monitor);
		sb = DaoSqlUtil.appendSql(sb, "type", type);
		sb = DaoSqlUtil.appendSql(sb, "status", status);
		sb.append("ORDER BY code asc ");

		Query query = hibernateDao.getSqlQuery(sb.toString());

		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQueryListInt(query, "monitor", monitor);
		query = DaoSqlUtil.appendQuery(query, "type", type);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		List<Object[]> queryResult = query.list();

		for (Object[] fields : queryResult) {
			IdNamePid idAndName = new IdNamePid();
			idAndName.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
			idAndName.setName(fields[1] == null? "":fields[1].toString());
			idAndName.setPid(fields[2] == null? 0:Long.parseLong(fields[2].toString()));
			result.add(idAndName);
		}
		return result;
	}

    @SuppressWarnings("unchecked")
    public List<IdNamePid> listByCondition(Integer monitor, Long formsid, Long type, Integer status) {
    	
    	List<IdNamePid> result = Lists.newArrayList();
    	
    	StringBuffer sb = new StringBuffer("select id,name,pid from indicator where 1=1 ");

		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSql(sb, "monitor", monitor);
		sb = DaoSqlUtil.appendSql(sb, "type", type);
		sb = DaoSqlUtil.appendSql(sb, "status", status);
		sb.append("ORDER BY code asc ");

    	Query query = hibernateDao.getSqlQuery(sb.toString());

		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQuery(query, "monitor", monitor);
		query = DaoSqlUtil.appendQuery(query, "type", type);
		query = DaoSqlUtil.appendQuery(query, "status", status);

    	List<Object[]> queryResult = query.list();
    	
    	for (Object[] fields : queryResult) {
    		IdNamePid idAndName = new IdNamePid();
    		idAndName.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
    		idAndName.setName(fields[1] == null? "":fields[1].toString());
    		idAndName.setPid(fields[2] == null? 0:Long.parseLong(fields[2].toString()));
    		result.add(idAndName);
    	}
    	return result;
    }

	@SuppressWarnings("unchecked")
	public List<Long> getTypeIdsByCondition(List<Long> id, Long formsid, Integer monitor, Long type, Integer status) {

		StringBuffer sb = new StringBuffer("select type from indicator where 1=1 ");
		sb = DaoSqlUtil.appendSqlListLong(sb, "id", id);
		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSql(sb, "monitor", monitor);
		sb = DaoSqlUtil.appendSql(sb, "type", type);
		sb = DaoSqlUtil.appendSql(sb, "status", status);
		sb.append("ORDER BY code asc ");

		Query query = hibernateDao.getSqlQuery(sb.toString());
		query = DaoSqlUtil.appendQueryListLong(query, "id", id);
		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQuery(query, "monitor", monitor);
		query = DaoSqlUtil.appendQuery(query, "type", type);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		List<Long> result = Lists.newArrayList();
		List<Object> queryResult = query.list();
		for (Object field : queryResult) {
			result.add(field == null ? 0:Long.parseLong(field.toString()));
		}
		return result;
	}

	public Integer getTotal(String name, Long  formsid, List<Long> types, Integer province, Integer status) {

		StringBuffer sb = new StringBuffer("select count(*) from indicator where 1=1 ");
		if(StringUtils.isNotBlank(name)){
			sb.append("and (name like:name or code like:name) ");
		}
		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSqlListLong(sb, "type", types);
		sb = DaoSqlUtil.appendSql(sb, "province", province);
		sb = DaoSqlUtil.appendSql(sb, "status", status);

		Query query = hibernateDao.getSqlQuery(sb.toString());
		if(StringUtils.isNotBlank(name)){
			query.setString("name","%" + name + "%");
		}
		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQueryListLong(query, "type", types);
		query = DaoSqlUtil.appendQuery(query, "province", province);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		BigInteger count = (BigInteger) query.uniqueResult();
		return (int) count.longValue();

	}

	public Integer getTotalOppositeProvince(String name, Long  formsid, List<Long> types, Integer province, Integer status) {

		StringBuffer sb = new StringBuffer("select count(*) from indicator where 1=1 ");

		if(StringUtils.isNotBlank(name)){
			sb.append("and (name like:name or code like:name) ");
		}

		sb = DaoSqlUtil.appendSql(sb, "formsid", formsid);
		sb = DaoSqlUtil.appendSqlListLong(sb, "type", types);
		sb = DaoSqlUtil.appendSqlOpposite(sb, "province", province);
		sb = DaoSqlUtil.appendSql(sb, "status", status);

		Query query = hibernateDao.getSqlQuery(sb.toString());

		if(StringUtils.isNotBlank(name)){
			query.setString("name","%" + name + "%");
		}

		query = DaoSqlUtil.appendQuery(query, "formsid", formsid);
		query = DaoSqlUtil.appendQueryListLong(query, "type", types);
		query = DaoSqlUtil.appendQuery(query, "province", province);
		query = DaoSqlUtil.appendQuery(query, "status", status);

		BigInteger count = (BigInteger) query.uniqueResult();
		return (int) count.longValue();
	}
    
}
